[Amazon Athena]一見json配列に見えるvarcharのデータをパースして集計できる形式に変換する

データアナリティクス事業本部の貞松です。最近は少々データレイクに想いを馳せています。

今回は業務で発生したAmazon Athenaのクエリ集計について、実現したい内容とその実現方法を解説します。

集計対象データと実現したい内容

集計対象データの中身

集計対象のテーブルデータは以下のようなものです。

id	items
1	[{"item_id":"1","score":"0.8351"},{"item_id":"2","score":"0.8026"},{"item_id":"3","score":"0.7885"},{"item_id":"4","score":"0.7789"},{"item_id":"5","score":"0.7699"}]
2	[{"item_id":"3","score":"0.8701"},{"item_id":"5","score":"0.8219"},{"item_id":"7","score":"0.8106"},{"item_id":"9","score":"0.7964"},{"item_id":"11","score":"0.7964"}]
…	…

はい、このデータのitemsカラム、パッと見json配列の顔をしていますが、データ型は「varchar」です。構造化されていないベタの文字列が入っています。

実現したい内容

このデータからitem_idごとにカウントしたり、scoreを集計したいのですが、このままでは当然集計できません。
集計する為に、この文字列をパース、要素を抽出して、以下のような縦持ちのテーブルデータに変換します。

item_id	score
1	0.8351
2	0.8026
3	0.7885
4	0.7789
5	0.7699
…	…

実現方法

まず前提となるAthenaのクエリエンジンについて触れた上で、実際に作成したクエリについて順を追って解説します。
最終的なクエリだけドンと載せてしまうと、使用する関数などの各処理がどういう動作をしているのかわからないまま結果だけ得るような形になってしまい「似てるけど違うケース」への応用が効きにくいという意図から、かなり細かく段階を踏んで説明しています。

Amazon Athenaのクエリエンジンについて

Amazon Athenaのクエリエンジンは、Presto 0.172に基づいています。Athenaでは、Prestoの機能の一部だけがサポートされます。
利用可能なPresto関数のリファレンスと考慮事項・制約事項については、下記の公式ドキュメントを参照してください。

文字列(varchar)をjsonにパースする

json_parse関数を使用して、varchar型の文字列をパースしてjson型に変換します。
json_parseは単純に文字列をjson構文で解析してjson型に変換してくれます。配列型のvalueに対してkeyが無いjson配列の文字列に対してcast関数を使ってjson型に変換しようとすると、うまくjson型に変換できなかった為json_parseを使用しています。

6.12. JSON Functions and Operators - json_parse - Presto 0.172 Documentation

select
  json_parse(items) as items_json
from
  items;

結果は以下のようになります。

items_json
[{"item_id":"1","score":"0.8351"},{"item_id":"2","score":"0.8026"},{"item_id":"3","score":"0.7885"},{"item_id":"4","score":"0.7789"},{"item_id":"5","score":"0.7699"}]
[{"item_id":"3","score":"0.8701"},{"item_id":"5","score":"0.8219"},{"item_id":"7","score":"0.8106"},{"item_id":"9","score":"0.7964"},{"item_id":"11","score":"0.7964"}]
…

結局パッと見は変わりませんが、typeof関数でデータ型を確認するとちゃんとjsonになっています。

select
  typeof(json_parse(items)) as items_json_type
from
  items
limit 1;

items_json_type
json

jsonからjsonの配列(array)にcastする

上記でvarcharからjsonに変換しましたが、中身を見ての通り、実際は配列なのでarray(json)にcastします。

select
  cast(json_parse(items) as array(json)) as items_json_array
from
  items;

結果は以下の通りです。やはり見た目は変わりませんが、ちゃんとarray(json)型になっています。

items_json_array
[{"item_id":"1","score":"0.8351"},{"item_id":"2","score":"0.8026"},{"item_id":"3","score":"0.7885"},{"item_id":"4","score":"0.7789"},{"item_id":"5","score":"0.7699"}]
[{"item_id":"3","score":"0.8701"},{"item_id":"5","score":"0.8219"},{"item_id":"7","score":"0.8106"},{"item_id":"9","score":"0.7964"},{"item_id":"11","score":"0.7964"}]
…

配列(array)をunnestする

ここからが本番です。
配列に含まれるjsonの要素をcross join unnest構文を使って縦持ちに変換します。
unnest関数はarrayまたはmapをリレーションに展開するために使用でき、arrayは1つの列に展開され、mapは2つの列（キー、値）に展開されます。

8.21. SELECT - unnest - Presto 0.172 Documentation

クエリを完結にする為、ここでは「cross join」を省略した記述でunnest構文を使用します。

with items_work as (
  select
    cast(json_parse(items) as array(json)) as items_json_array
  from
    items
)
select
  items_json
from
  items_work, unnest(items_json_array) AS t(items_json)
;

実行結果は以下のようになります。json型のデータが縦持ちに変換されています。

items_json
{"item_id":"1","score":"0.8351"}
{"item_id":"2","score":"0.8026"}
{"item_id":"3","score":"0.7885"}
{"item_id":"4","score":"0.7789"}
{"item_id":"5","score":"0.7699"}
…

unnestしたjsonからjson_extractで要素を抽出する

最後にunnestされたjsonに対して、json_extract関数を使うことでjson内の各要素を抽出します。
json_extract関数は、対象となるjson型のカラム名と抽出したいjson要素のパスを引数として渡すことでデータを抽出できます。
json要素のパスは$.の後に要素のkeyを指定します。

6.12. JSON Functions and Operators - json_extract - Presto 0.172 Documentation

下記が最終的なクエリです。

with items_work as (
  select
    cast(json_parse(items) as array(json)) as items_json_array
  from
    items
)
select
  cast(json_extract(items, '$.item_id') as integer) as item_id,
  cast(json_extract(items, '$.score') as double) as score
from
  items_work, unnest(items_json_array) AS t(items)
;

実行結果は以下の通りです。当初実現したかった内容の通り、item_idとscoreの組合せが縦持ちになったデータとして出力されています。
あとは煮るなり焼くなり好きなように集計することが可能になりました。

item_id	score
1	0.8351
2	0.8026
3	0.7885
4	0.7789
5	0.7699
…	…

まとめ

Athenaのテーブルに入っている一見json配列に見えるvarchar型のデータをパースして集計できる形に変換する過程を解説しました。そもそも何でこんな形式でデータが入ってるかは聞かないお約束。
Athenaのクエリでjsonや配列を扱う際の参考になれば幸いです。